Automated identification of complex transformations and generation of subscriptions for data replication

ABSTRACT

According to embodiments of the present invention, machines, systems, methods and computer program products as part of a data replication process are provided. One or more complex transformations are identified from source code files of installed software products on a target system. A subscription is created for each complex transformation, the subscription containing instructions for transforming data within the source system into a form compatible with the target system. The instructions are executed within the target system to transform source data of the source system into a form compatible with the target system.

BACKGROUND

1. Technical Field

Present invention embodiments relate to data replication, and morespecifically, to automatically identifying complex transformations andgenerating subscriptions for complex transformations of data duringreplication of data from a source system to a target system.

2. Discussion of the Related Art

Software upgrades typically result in large amounts of downtime.Typically, a system or database is taken offline and software upgradesare performed. Data may be identified and transformed using a manual anderror prone process to place the data in a form that is compatible withthe upgraded software.

For example, when moving data between code levels, e.g., a currentversion of a code level at a source system to an upgraded version of acode level at a target system, the underlying data model may change.Variables, tables, or portions of tables and so forth within thedatabase of the source system may no longer be present or may have beentransformed or combined within the database of the target system. Insuch cases, an administrator may need to manually review software codeto identify such changes in the underlying data model to upgrade thedata accordingly. Such processes may lead to lengthy downtimes withlarge amounts of loss in consumer productivity. Customers desire nearzero system downtime when upgrading system product software.

SUMMARY

According to embodiments of the present invention, machines, systems,methods and computer program products for generating subscriptions aspart of a data replication process are provided. One or more complextransformations are identified from source code files of installedsoftware products on a target system. A subscription is created for eachcomplex transformation, and contains instructions for transforming datawithin the source system into a form compatible with the target system.Each instruction is executed within the target system to transformsource data of the source system into a form compatible with the targetsystem. In preferred embodiments, complex transformations may utilizespecific commands, e.g., SQL commands particular to a programminglanguage of a database.

Accordingly, present invention embodiments include automating complextransformations of a data replication process. Present inventionembodiments also include automatic generation of objects, e.g.,system-wide objects, for replicating data from a source system to atarget system having different code levels. An object may comprisecomputer executable instructions for transforming data. In preferredembodiments, subscriptions may comprise one or more objects orreferences to files comprising one or more objects. Original data on thesource system can be transformed using the data objects and thenreplicated to the target system in real-time or near real-time. Byautomatically identifying complex transformations and generatingcorresponding subscriptions, data replication may be automated andperformed in a more efficient manner, as compared with manuallyidentifying complex transformations and transforming associated data byhand. Transformation use cases may be identified and correspondingtransformation objects identified in an automated manner. Accordingly,system downtime from the data replication process may be reduced.Additionally, present invention embodiments provide a more accurate,reliable process, as compared to manually driven transformations,reducing errors from user-based transformation processes.

In preferred embodiments, the subscription file may provide fine grainaccess control (FGAC), limiting access or fine tuning access, toindividual items of data to be transformed as part of the replicationprocess. FGAC allows data in particular tables, columns, rows,variables, etc. to be transformed, while allowing other data to remainunaffected by the transformation. Accordingly, FGAC provides forautomation of manually driven processes in an efficient, targetedmanner, allowing data operations to occur on specific data withoutmanual involvement.

In other preferred embodiments, fine grain access control may include,for a plurality of tables identified to be impacted by the complextransformation, determining a subset of the plurality of tablescomprising data that is not to be transformed and inactivating access tothis subset of tables. Accordingly, the complex transformation isperformed only on the tables that have not been inactivated, allowingdata operations to be performed on specific data.

In other preferred embodiments, the subscription may further compriseone or more references to one or more executable files containinginstructions for transforming data, the instructions comprisinggenerated source code. Generated source code files may be created byselecting a source code file of the target system containing a complextransformation, updating the source code file to transform data of thesource system into a form compatible with the target system, and savingthe updated/generated source code file, allowing complex transformationsto be performed at a future point in time as part of a data replicationprocess.

In still other preferred embodiments, an order of installation of thesource code files of installed software products on the target system isobtained. The source code files are parsed in the order of installationto identify a corresponding order of the complex transformations, and acache of complex transformations are generated, wherein each table inthe cache lists the identified complex transformations in thecorresponding order based on the respective source code file.Accordingly, identifying the ordering of the target system allowsconsistency and accuracy with regard to applying transformations in anordered manner to the data to be replicated, as well as allows formultiple installed products and version combinations to be accountedfor.

Other preferred embodiments include generating a report listingdifferences between the source system and the target system, identifyingfrom the report, items of data that are present within the targetsystem, with such items serving as input when generating a subscription.Generating a subscription for each complex transformation having acorresponding item of data in the target system. By identifying itemsthat are no longer present with the target system, the data replicationprocess may be further accelerated by reducing unnecessary computations.

These and other aspects, features and embodiments of the presentinvention will be understood with reference to the drawing figures andthe detailed description herein, and may be realized by way of thevarious elements and combinations particularly pointed out in theappended claims. It is to be understood that both the foregoing generaldescription and the following brief description of the drawings anddetailed description are examples and explanatory of preferredembodiments of the invention, and are not restrictive of presentinvention embodiments, as claimed.

BRIEF DESCRIPTION OF THE SEVERAL VIEWS OF THE DRAWINGS

Generally, like reference numerals in the various figures are utilizedto designate like components.

FIG. 1 is an illustration showing an example system for automaticallygenerating subscriptions in accordance with an embodiment of the presentinvention.

FIG. 2 is a procedural flowchart showing example operations of analyzingand caching ordered transformations in accordance with an embodiment ofthe present invention.

FIG. 3 is a procedural flowchart showing example operations ofgenerating subscriptions in accordance with an embodiment of the presentinvention.

FIG. 4 is a procedural flowchart showing example operations of creatinga user exit in accordance with an embodiment of the present invention.

FIG. 5 is a procedural flowchart showing example operations ofgenerating a modified source code file with one or more complextransformations in accordance with an embodiment of the presentinvention.

FIGS. 6A-6G show examples of user exits in accordance with embodimentsof the present invention.

DETAILED DESCRIPTION

When moving data between code levels, e.g., a current version of a codelevel at a source system to an upgraded version of a code level at atarget system, the underlying data model may have changed. Accordingly,it is desirable to limit system downtime from such data transformations.Present invention embodiments evaluate differences between the sourcecode of the source system and the source code of the target system toidentify complex transformations. Once these transformations areidentified, objects may be automatically generated to perform complextransformations on data being replicated to the target system, as partof a data replication process, so that the data is compatible with thetarget system. Transformations may occur in real-time or near real-time.During replication, the objects may be executed, at the target system,on incoming data to transform the data into a compatible format with thetarget system.

Accordingly, preferred embodiments include automatic generation ofobjects for replicating data from a source system to a target systemwith different code levels. By automatically identifying complextransformations and generating corresponding subscriptions, datareplication may be automated and performed in a more efficient manner,as compared to manually identifying complex transformations andtransforming associated data by hand. Accordingly, system downtime fromthe data replication process may be reduced. Additionally, presentinvention embodiments provide a more accurate, reliable process, ascompared to manually driven transformations, reducing errors fromuser-based manual transformations.

Additionally, in other preferred embodiments, identification of complextransformations and generation of corresponding objects may be performedprior to data replication in the upgrade process, and the data may betransformed immediately thereafter or at a future point in time.

An example environment for use with present invention embodiments isillustrated in FIG. 1. Specifically, the environment includes one ormore client or end-user systems 100, and one or more server systems 200.Server system 200 and client system 100 may be remote from each otherand communicate over a network 120. The network may be implemented byany number of any suitable communications media (e.g., wide area network(WAN), local area network (LAN), Internet, Intranet, etc.).Alternatively, server system 200 and client system 100 may be local toeach other, and communicate via any appropriate local communicationmedium (e.g., local area network (LAN), hardwire, wireless link,Intranet, etc.).

Client system 100 enables users to connect remotely to server system 200to manage identification of complex transformations and generation ofcorresponding objects as part of a data replication process. It isunderstood that present invention embodiments do not require a clientsystem (presence of a client system is optional), and may operateindependently from a client system. Server system 200 may connect to asource database or system 300 and a target database or system 400 toobtain various types of information needed for data replication. Server200 may comprise a database difference module 55 to identify databaseschema and metadata differences between the source system 300 and thetarget system 400, a process and product ordering module 45 to determinesource code levels between a source system and a target system and todetermine the order in which various software applications have beeninstalled, as well as a subscription generation and FGAC control module65, which generates subscriptions, including FGAC, to be used during theprocess of data replication. In other embodiments, presence of a serversystem 200 is also optional.

A database system, e.g., source database or system 300 and targetdatabase or system 400, may store various information needed for objectgeneration (e.g., product versions, release versions, build versions,dates of installation, types of transformations, e.g., simple orcomplex, database data, etc.). Subscription generation module 65 may beutilized to identify complex transformations and generate correspondingobjects for data transformation. The source and target systems may beimplemented by any conventional or other database or storage unit, maybe local to or remote from server system 200 and client system 100, andmay communicate via any appropriate communication medium (e.g., localarea network (LAN), wide area network (WAN), Internet, hardwire,wireless link, Intranet, etc.). The client system 100 may present agraphical user (e.g., GUI, etc.) or other interface (e.g., command lineprompts, menu screens, etc.) to connect to the server system 200 andview information relating to the data transformation process (e.g.,cached ordered tables, product versions, product installation orders,subscriptions, database difference files, etc.).

Server system 200 and client system 100 may be implemented by anyconventional or other computer system preferably equipped with a displayor monitor, a base (e.g., including at least one processor 15, one ormore memories 35 and/or internal or external network interfaces orcommunications devices 25 (e.g., modem, network cards, etc.)), optionalinput devices (e.g., a keyboard, mouse or other input device), and anycommercially available and custom software (e.g., server/communicationssoftware, modules 45, 55 and 65, browser/interface software, etc.).

In still other embodiments, presence of a client and/or a server isoptional. A stand-alone mode of operation may be employed in which acomputing system stores or has access to data (e.g., product versions,product installation orders, source and target code, etc.), and includesmodules 45-65 to identify orderings, create subscriptions, identifysystem differences, etc. A graphical user (e.g., GUI, etc.) or otherinterface (e.g., command line prompts, menu screens, etc.) may solicitinformation from a corresponding user pertaining to the desired filesand analysis, and may output generated source code files based on theanalysis results.

A module may include one or more modules or units to perform the variousfunctions of present invention embodiments described throughout thisapplication. The various modules (e.g., ordering module 45, subscriptiongeneration module 65, database difference module 55, etc.) may beimplemented by any combination of any quantity of software and/orhardware modules or units, and may reside within memory 35 of the serverfor execution by processor 15.

Present invention embodiments are compatible with any operating systemincluding Linux®, Unix®, Windows®, etc. and is also executable inmulti-platform environments and environments with multiple databases.

FIG. 1 also shows an example flow of data between modules in accordancewith an embodiment of the present invention. The database differencemodule 55 may generate a database difference report comprisingdifferences in schema and metadata between the source database and thetarget database that may be used by both ordering module 45 andsubscription generation and FGAC module 65. The database differencereport may comprise a listing of underlying changes to the system, e.g.,variables, rows, columns, tables, etc., of the target system as comparedto the source system. In some embodiments, ordering module 45 mayutilize the information in the database difference report to determinewhether a complex transformation involving a particular table, row,column or variable should be included in a cache of orderedtransformations. In other embodiments, the database difference reportmay be utilized by subscription generation module 65 to determinewhether to generate a subscription pertaining to a particular table,row, column or variable. Ordering module 45 may determine source codelevels between a source system and a target system. The output ofordering module 45, which is the cache of ordered transformations, isthen utilized by subscription generation and FGAC module 65 to generatesubscriptions.

FIG. 2 shows an example flow chart for generating a cached table ofordered transformations, according to modules 45-55. Identifying thepresence and ordering of the source and target systems allowsconsistency and accuracy with regard to ordering of data transformationswithin the target system.

At operation 210, current product versions from the source and targetsystem are retrieved and a product process order (order of installationof software products) is obtained. For example, current product versionsfrom the source database 300 and the target database 400 along with theprocess order (e.g., the order of the installed software code base,which may include installed customer, base product, add-ons, andindustry software solutions for the source and target systems) areobtained. At operation 220, based upon the process/product order, a listof all source code files (SCFs) is obtained, including one or more of asoftware version, release, modification, build, etc., for each softwareproduct installed on the source system 300 (current software version)and the target system 400 (upgraded software version). SCFs can be inthe form of files containing database language directives such as SQLcommands or programming languages, such as Java, containing logic forconstruction of database language directives. By building a list of SCFsthat are unique to the target, the corresponding software code may bescrutinized to determine what types of transformations have occurred aswell as the order in which transformations have occurred.

At operation 230, an SCF is opened. At operation 240, the SCF is parsedto analyze Structured Query Language (SQL) statements within the SCF toidentify transformations, e.g., simple or complex transformations.Identification of transformations may be performed using various searchpatterns. Simple transformations may include simple functions such asdata type conversions, column length (substring), supplying defaultvalues for required fields, etc. Examples of complex transformationsinclude transformations that span across multiple scripts, move datafrom multiple columns to one column, move data from multiple tables toone table, changing column data using a formula or algorithm, etc. Atoperation 250, it is determined whether the end of the SCF has beenreached. If an end of file has been reached, at operation 280, it isdetermined whether another SCF is present in the list of SCFs forparsing. If another SCF is present in the list of SCFs within theprocess order list, the process continues at operation 230, and iteratesthrough all available SCFs. If the last SCF in the process order filehas been parsed, the process continues to “A” at block 290.

Referring to operation 250, if the end of the SCF has not been reached,at operation 260, SQL code (e.g., a line, block, or segment of code,etc.) may be evaluated to determine if a complex transformation existsin the code being evaluated. If a complex transformation exists, atoperation 270, a hash of the table name and the SQL or SCF is cached ina table of ordered transformations. If a complex transformation does notexist, a subsequent portion of the SCF is parsed at operation 240.

Accordingly, each portion of code in each SCF may be parsed to identifycomplex transformations. The identified complex transformations arecached, e.g., by table name, reflecting the ordering of transformationsas determined by the process order. The cache keeps track of thetransformations found in each file, as well as interactions betweentransformations from multiple files. As an example, multiple SCFs maycontain code which transforms data for the same table or transforms datafor dependent tables.

The source SCFs and the target SCFs are compared to find differencesbetween the two systems. Changes between the source and the targetsystems may be identified, e.g., additional files, different fileversions, etc., and the process described in FIG. 2 may be utilized toidentify and cache complex operations within SCF files. Accordingly inFIG. 1, SCFs that are common to both the source system 300 and thetarget system 400 may be excluded, and analysis limited to differencesin SCFs between the source and target systems, thus, reducingunnecessary computation.

FIG. 3 shows a flow chart for creating subscriptions for transformationsbased upon information from database or system models (e.g., a databasedifference report), and an ordered table of transformations.

FGAC is also shown in the context of creating subscriptions for complextransformations. FGAC limits access or fine tunes access to individualitems of data to be transformed. For example, FGAC allows data inparticular tables, columns, rows, variables, etc. to be transformed,while allowing data in other tables to remain unaffected. FGAC may alsobe implemented to facilitate operating on one row of data at a time,thereby reducing computations (as compared to computing upon an entiretable) during data replication.

In some embodiments, the target system may have undergone changes to thedatabase model itself. Such changes may include changes in data types,changes in variable length, new columns, derived columns (e.g., columnsthat are generated from data in other columns), deleted tables, deletedcolumns or rows, and so forth. In order to replicate data from thesource system 300 to the target system 400, underlying changes to thedatabase or system model may be captured in a database difference reportgenerated by module 55. The information in the database differencereport along with a listing of ordered transformations may be used togenerate replication subscriptions by module 65. Replicationsubscriptions may comprise a particular manner of combining data, aparticular order of combining data, particular table(s), numbers and/ortypes of transformations, etc. By identifying items that are no longerpresent within the target system, the data replication process may befurther accelerated by reducing unnecessary computation.

It is understood that additional logic may be added to FIG. 3 to captureadditional aspects of changes to database or system models.

Once all SCFs are processed (from operation 280), at operation 305, thesubscription generation process begins by selecting each table in acomparison list, a list formed by comparing the tables that exist inboth the source and target databases, and generating the replicationsubscription using the database difference report. It is understood thatthis step will repeat as long as additional tables in the list arepresent, so that one or more subscriptions are created for each table inthe list. Once each table in the list has been considered, the processcompletes at operation 315. Otherwise, the process proceeds to operation320.

Operations 320-345 involve aspects of accounting for changes in adatabase or system model, e.g., as identified in a database differencefile. At operation 320, it is determined whether the selected table isin a “doNotGenerate” list. If the table is present in the doNotGeneratelist, a subscription will not be created for that table, as shown atoperation 325. The doNotGenerate list allows a user to specify entiretables to be omitted from the subscription generation process. If thetable is not included in the doNotGenerate list, then the processproceeds to operation 330.

At operation 330, it is determined whether a table is deleted in the newschema corresponding to the target system. For instance, if targetsystem 400 no longer contains a selected table, then a subscription doesnot need to be created for that table as the target system 400 does nothave a place to store corresponding data.

If a corresponding table is present in the target system, then theprocess continues to operation 335 to determine whether or not eachcolumn of the table has been retained in the target system 400. Atoperation 335, a column in the table is selected. Once a column isselected, at operation 340, it is determined whether the column has beendeleted in the new schema (e.g., in the target system 400). If thecolumn has been deleted, then a subscription will not be created forthat column, and the process continues, with the next column beingselected at operation 335. If the column is not deleted in the newschema, a subscription is created at operation 345, e.g., the column ismapped, and the process continues to operation 335. This process keepstrack of each column for which a subscription is needed. In someembodiments, the database difference report shows which tables andcolumns are deleted, new, changed or have remained the same. If thecolumn has changed, the database difference report may provideinformation on specific differences. A set of differences files may becreated that hold table/column information for both the source andtarget systems. As processing is performed, the code may traversethrough these files to build a temporary view of the informationrequired for the final subscription. It is understood that this processrepeats for each column in a table.

Once all columns have been evaluated, the process continues tooperations 350-380, which handle subscription generation. At operation350, it is determined whether the table contains transformations. Iftransformations are not present, then a one-to-one mapping, for eachnon-deleted column, is created within a subscription at operation 360,and the process proceeds to operation 305 to evaluate the next table. Iftransformations are present, then at operations 355 and 370, the type oftransformation is determined, e.g., whether the transformation is simpleor complex. If the type of transformation is determined to be simple,then within a subscription, a mapping using a simple transformationfunction (e.g., a data type conversion, column lengths, a substring ortruncation, required fields with default values, etc.) is created atoperation 365. If the type of transformation is determined to becomplex, as shown at operation 370, then a subscription may be created,which may incorporate FGAC and one or more user exits, as shown at block375. It is understood that if a table has both simple and complextransformations, each type will be handled appropriately.

In some embodiments, and with reference to block 375, a subscription maycall out to a user exit containing generated source code, with thesource code carrying out the transformation. A user exit is code thatmay run or call other source code. For example, a user exit may extendthe capabilities of a system by calling other source code or an extendedclass of source code to perform a specified function, e.g., calling fromwithin a Java program, an extended class within Java to perform afunction.

According to preferred embodiments, a user exit comprising source codeis automatically generated by the system, and the source code of theuser exit may perform the data transformation, e.g., on acolumn-by-column basis. FGAC may be utilized to create context, createstored schemas or procedures, create policy or permissions, as well ascreate predicates, secure triggers, and activate row (or column ortable) access control. Advantages of FGAC include limiting access toparticular tables, rows, and columns of data as well as increasingefficiency of the data replication process.

Upon completion of operation 375 or of operation 365, the processcontinues at operation 305. If the table does not have any simple orcomplex transformations and does not include a one-to-one mapping, anerror is written to the error log at operation 380, and the processcontinues to operation 305.

As previously discussed, one or more data transformations may need FGACto be defined. Typical upgrades may update the entire table and all datawithin that table. When performing the replication process acrosssystems, it may be desirable to update the database one row (or column)at a time, instead of an entire table, as data may be received at atarget system one row (or column) at a time.

In preferred embodiments, computations from a subscription are performedon one row of data at a time, as the data is arriving at the targetsystem from the source system, using FGAC, which provides the ability tooperate on one row of data at a time. Accordingly, preferred embodimentscreate subscriptions along with all needed FGAC at the time thesubscription is created, to execute as part of the data replicationprocess.

FIG. 4 shows an example flowchart of operations performed by module 65of creating user exits along with utilizing FGAC during the process ofcreating subscriptions and user exits. This flowchart is an extension ofoperation 375 of FIG. 3. A subscription is a replication object filewhich may have callouts to a user exit, which may have been generatedfor complex transformations that transform data from the source system300 into a compatible format with the target system 400. In someembodiments, a user exit can have an embedded user exit, e.g., a userexit within a user exit. By identifying such instructions and generatingmodified source code, e.g., to perform transformations in a programminglanguage different from the programming language of the system,automation of complex transformations is achieved and manual analysis ofsource code is avoided.

Operations 420-440 and 470 involve aspects of FGAC, while operations450-465 involve creation of commands within a user exit. At operation420, a clear predicate FGAC command is created for the main (current)table involved in the transformation transaction. At operation 425, aset predicate FGAC command is created for the main transaction tableusing a primary key. In some embodiments, the primary key is used toidentify a row of data, allowing data manipulation to occur only forthat row. At operation 430, if a complex transformation exists in theSCF that affects other tables (that should not be affected by thistransaction, a clear predicate FGAC command is created at operation 435for tables not in the current transaction, and a set predicate FGACcommand is created using a non-existing primary key value at operation440. By utilizing FGAC, these related tables will not be affected by thetransaction. The process (operations 430-440) continues until allcomplex transformations for related tables have been evaluated.

The process continues at operation 445, in which a transformation isselected, and each transformation is evaluated in the order that theupgrade would have been processed in (e.g., applying transformations ina particular order to specific data based upon the process orderpreviously established). At operation 450, if the transformation isdetermined to comprise an SQL statement, then the process continues atoperation 455, in which an SQL command is written to the user exit. Atoperation 460, if the transformation is determined to comprise a sourcecode statement different from the computing language of the database(e.g., a Java command, or other custom command), then the processcontinues at operation 465 in which a call statement to a child userexit is written in the parent user exit. If the transformation wasevaluated for the presence of SQL statements and source code statements,and it was determined that the transformation did not match eithercondition, then an error is generated at operation 480.

Accordingly, a subscription may reference a user exit, which comprises aseries of executable statements, e.g., from SQL or another executablelanguage, which may be run to transform data into a compatible form withthe target system.

Once the relevant statements are written to the user exit, the processcontinues at operation 445, repeating the process for eachtransformation provided, until all transformations have been evaluated.Once all transformations have been evaluated, the process continues tooperation 470, in which a “clear predicate” FGAC command is called forall tables related to the transaction, e.g., tables for which a setpredicate command was issued at operation 440, turning off FGAC, and theprocess of operation 375 concludes, returning to operation 305.

FIG. 5 shows an example flowchart of how modified source code isgenerated for non-SQL transformations, with reference to operation 465of FIG. 4 and module 65. By generating a modified source code file,transformations may be conducted at a later point in time, as part of adata replication process.

At operation 510, a source code file containing a transformation isselected. At operation 515, a copy of the source file is made, and thefile name/class name is changed. New constructors may be added asneeded. Operations that are not needed to place the data in a compatiblestate with the target system (e.g., SQL commits, SQL rollbacks, etc.)may be removed. This process may be repeated for each SCF.

At operation 520, an extended class is selected. Similar to operation515, a copy of the source code file is made and the file name/class nameis changed. New constructors may be added as needed. Operations that arenot needed to place the data in a compatible state with the targetsystem (e.g., SQL commits, SQL rollbacks, etc.) may be removed. Changeimports or include of extended classes may also be included. Thisprocess may be repeated for each extended class.

Once all SCFs have been processed, the process concludes at 530,returning to operation 465. All the complex transformations, e.g., SQLand/or Java source code, will be applied in the order the upgrade wouldhave processed them.

FIGS. 6A-6G show examples of generated source code user exits. The userexit may call out or execute a generated source code file if a table hasany complex transformations, e.g., an example output of FIG. 4 may showa set of instructions that may be carried out in order to transform datainto a format compatible with a target database 400. This file may beloaded into a data replication system and executed at the time that datareplication is being carried out. In general, generated source codefiles may contain instructions pertaining to FGAC, include one or moreuser exits, and contain instructions for transforming data usingdatabase commands, e.g., SQL commands.

FIGS. 6A-6C show an example of a user exit. Components 610 show aspectsof FGAC. A clear predicate call is initially executed for a table called“assetmeter”. In this example, the FGAC predicate is set for“assetmeter” using the value of the variable “id”, which may be passedin by the replication system. This value is a unique key for thistable's transaction, which was passed in from the replication system.Tables associated with “locationmeter” and “measurement” are cleared andthen set to a value of “−1”. This value does not exist for a unique keyin the table, and may be read from a properties file, and is used toindicate that these tables are not to be modified by the currenttransaction. Accordingly, FGAC allows data only within “assetmeter” tobe transformed, with corresponding data in other tables, e.g.,“locationmeter” and “measurement”, not being affected by execution ofthe user exit.

Component 620 shows a user exit call to a modified source code file fora complex transformation. SQL statements may be included here (not shownin this example) as well. In this example, a Java file is included forexecution. A preferred embodiment may include both SQL statements andmodified source code files utilized for automating aspects of the datareplication process. Accordingly, this section may include SQLstatements as well as modified source code files, e.g., the output ofoperations 455 and 465 of FIG. 4. When these statements are executed,only “assetmeter”, the record that the transaction is for, will beaffected. If a source code file contains transformations for more thanone table, then FGAC will set the unique key value to a value that doesnot exist in the target system, preventing these tables from beingincluded in the transaction. This unique key value can be read from aproperties file. Modified source code files may include a variety ofprogramming languages including Java, C, XML, Text, etc.

Components 630 show another set of clear predicate commands beingissued, turning FGAC off.

FIGS. 6D-6G show additional examples of generated source code,comprising one or more user exits. Components 640 show aspects of FGAC.A clear predicate call is initially executed to clear the predicate fora table called “workorder”, which is then selected, e.g., using thevalue of the variable “id” (a unique key for the table's transaction),which may be passed in by the replication system. In this example, FGACis only set for the table that this transaction is for, e.g., theworkorder table.

Component 650 may include SQL statements, e.g., generated as part of theoutput of operation 455. Component 660 shows an example of a user exitcall to a modified source code file that performs complextransformations, e.g., the output of operation 465 of FIG. 4. Components670 include calls to SQL commands that are performed on tables otherthan the table that the transaction is for. In this example, clear andset predicate commands are not called, and therefore, these tables maybe accessed by the complex transformation. Component 680 shows aspectsof FGAC, e.g., a clear predicate commands is issued, turning FGAC offwith regard to table “workorder.”

Advantages of present invention embodiments include: dynamicallyidentifying all transformation use cases and generating correspondingtransformation objects, accounting for multiple products and versioncombinations, maintaining transformation logic in the SCF during thedata replication process, ensuring the transformation object logicoperates on a single row in a table for upgrade purposes by isolatingdata operations using FGAC, avoiding manual analysis of source code filelogic, which is time consuming, cost prohibitive and error prone, andapplying transformations in an ordered fashion in a real-time manner. Bytranslating aspects of the data replication process into transformationobjects (i.e., code development) in an automated manner, a manualprocess that was once time consuming and error prone, becomes highlyefficient and reduces system down-time.

Preferred embodiments provide for automatically identifying complextransformations and generating subscriptions or mappings forcorresponding data transformations. The subscriptions may call sourcecode files (e.g., also referred to as user exits) containingtransformations that when executed, transform data stored in a formatwithin the source system into a format compatible with the targetsystem. The subscriptions and associated files may be generated andstored for use at a subsequent point in time, e.g., during the processof data replication.

It will be appreciated that the embodiments described above andillustrated in the drawings represent only a few of the many ways ofimplementing embodiments for automatically identifying complextransformations and generating subscriptions.

The environment of the present invention embodiments may include anynumber of computer or other processing systems (e.g., client or end-usersystems, source and target systems, server systems, etc.) and databasesor other repositories arranged in any desired fashion, where the presentinvention embodiments may be applied to any desired type of computingenvironment (e.g., cloud computing, client-server, network computing,mainframe, stand-alone systems, etc.). The computer or other processingsystems employed by the present invention embodiments may be implementedby any number of any personal or other type of computer or processingsystem (e.g., desktop, laptop, PDA, mobile devices, etc.), and mayinclude any commercially available operating system and any combinationof commercially available and custom software (e.g., browser software,communications software, server software, etc.). These systems mayinclude any types of monitors and input devices (e.g., keyboard, mouse,voice recognition, etc.) to enter and/or view information.

It is to be understood that the software (e.g., ordering module 45,subscription generation and FGAC module 65, database difference module55, etc.) of the present invention embodiments may be implemented in anydesired computer language and could be developed by one of ordinaryskill in the computer arts based on the functional descriptionscontained in the specification and flow charts illustrated in thedrawings. Further, any references herein of software performing variousfunctions generally refer to computer systems or processors performingthose functions under software control. The computer systems of thepresent invention embodiments may alternatively be implemented by anytype of hardware and/or other processing circuitry.

The various functions of the computer or other processing systems may bedistributed in any manner among any number of software and/or hardwaremodules or units, processing or computer systems and/or circuitry, wherethe computer or processing systems may be disposed locally or remotelyof each other and communicate via any suitable communications medium(e.g., LAN, WAN, Intranet, Internet, hardwire, modem connection,wireless, etc.). For example, the functions of the present inventionembodiments may be distributed in any manner among the variousend-user/client and server systems, and/or any other intermediaryprocessing devices. The software and/or algorithms described above andillustrated in the flow charts may be modified in any manner thataccomplishes the functions described herein. In addition, the functionsin the flow charts or description may be performed in any order thataccomplishes a desired operation.

The software of the present invention embodiments (e.g., ordering module45, subscription generation and FGAC module 65, database differencemodule 55, etc.) may be available on a non-transitory computer useablemedium (e.g., magnetic or optical mediums, magneto-optic mediums, floppydiskettes, CD-ROM, DVD, memory devices, etc.) of a stationary orportable program product apparatus or device for use with stand-alonesystems or systems connected by a network or other communicationsmedium.

The communication network may be implemented by any number of any typeof communications network (e.g., LAN, WAN, Internet, Intranet, VPN,etc.). The computer or other processing systems of the present inventionembodiments may include any conventional or other communications devicesto communicate over the network via any conventional or other protocols.The computer or other processing systems may utilize any type ofconnection (e.g., wired, wireless, etc.) for access to the network.Local communication media may be implemented by any suitablecommunication media (e.g., local area network (LAN), hardwire, wirelesslink, Intranet, etc.).

The system may employ any number of any conventional or other databases,data stores or storage structures (e.g., files, databases, datastructures, data or other repositories, etc.) to store information(e.g., cached ordered tables, product versions, installation orders,subscriptions, database difference files, etc.). The database system maybe implemented by any number of any conventional or other databases,data stores or storage structures (e.g., files, databases, datastructures, data or other repositories, etc.) to store information(e.g., cached ordered tables, product versions, installation orders,subscriptions, database difference files, etc.). The database system maybe included within or coupled to the server and/or client systems. Thedatabase systems and/or storage structures may be remote from or localto the computer or other processing systems, and may store any desireddata (e.g., data relating to transactions, data stored in databases,etc.).

The present invention embodiments may employ any number of any type ofuser interface (e.g., Graphical User Interface (GUI), command-line,prompt, etc.) for obtaining or providing information (e.g., cachedordered tables, product versions, installation orders, subscriptions,database difference files, etc.), where the interface may include anyinformation arranged in any fashion. The interface may include anynumber of any types of input or actuation mechanisms (e.g., buttons,icons, fields, boxes, links, etc.) disposed at any locations toenter/display information and initiate desired actions via any suitableinput devices (e.g., mouse, keyboard, etc.). The interface screens mayinclude any suitable actuators (e.g., links, tabs, etc.) to navigatebetween the screens in any fashion.

The report may include any information arranged in any fashion, and maybe configurable based on rules or other criteria to provide desiredinformation to a user (e.g., cached ordered tables, product versions,installation orders, subscriptions, database difference files, etc.).

The present invention embodiments are not limited to the specific tasksor algorithms described above, but may be utilized for migrationprocesses in general.

The terminology used herein is for the purpose of describing particularembodiments only and is not intended to be limiting of the invention. Asused herein, the singular forms “a”, “an” and “the” are intended toinclude the plural forms as well, unless the context clearly indicatesotherwise. It will be further understood that the terms “comprises”,“comprising”, “includes”, “including”, “has”, “have”, “having”, “with”and the like, when used in this specification, specify the presence ofstated features, integers, steps, operations, elements, and/orcomponents, but do not preclude the presence or addition of one or moreother features, integers, steps, operations, elements, components,and/or groups thereof.

The corresponding structures, materials, acts, and equivalents of allmeans or step plus function elements in the claims below are intended toinclude any structure, material, or act for performing the function incombination with other claimed elements as specifically claimed. Thedescription of the present invention has been presented for purposes ofillustration and description, but is not intended to be exhaustive orlimited to the invention in the form disclosed. Many modifications andvariations will be apparent to those of ordinary skill in the artwithout departing from the scope and spirit of the invention. Theembodiment was chosen and described in order to best explain theprinciples of the invention and the practical application, and to enableothers of ordinary skill in the art to understand the invention forvarious embodiments with various modifications as are suited to theparticular use contemplated.

The descriptions of the various embodiments of the present inventionhave been presented for purposes of illustration, but are not intendedto be exhaustive or limited to the embodiments disclosed. Manymodifications and variations will be apparent to those of ordinary skillin the art without departing from the scope and spirit of the describedembodiments. The terminology used herein was chosen to best explain theprinciples of the embodiments, the practical application or technicalimprovement over technologies found in the marketplace, or to enableothers of ordinary skill in the art to understand the embodimentsdisclosed herein.

The present invention may be a system, a method, and/or a computerprogram product. The computer program product may include a computerreadable storage medium (or media) having computer readable programinstructions thereon for causing a processor to carry out aspects of thepresent invention.

The computer readable storage medium can be a tangible device that canretain and store instructions for use by an instruction executiondevice. The computer readable storage medium may be, for example, but isnot limited to, an electronic storage device, a magnetic storage device,an optical storage device, an electromagnetic storage device, asemiconductor storage device, or any suitable combination of theforegoing. A non-exhaustive list of more specific examples of thecomputer readable storage medium includes the following: a portablecomputer diskette, a hard disk, a random access memory (RAM), aread-only memory (ROM), an erasable programmable read-only memory (EPROMor Flash memory), a static random access memory (SRAM), a portablecompact disc read-only memory (CD-ROM), a digital versatile disk (DVD),a memory stick, a floppy disk, a mechanically encoded device such aspunch-cards or raised structures in a groove having instructionsrecorded thereon, and any suitable combination of the foregoing. Acomputer readable storage medium, as used herein, is not to be construedas being transitory signals per se, such as radio waves or other freelypropagating electromagnetic waves, electromagnetic waves propagatingthrough a waveguide or other transmission media (e.g., light pulsespassing through a fiber-optic cable), or electrical signals transmittedthrough a wire.

Computer readable program instructions described herein can bedownloaded to respective computing/processing devices from a computerreadable storage medium or to an external computer or external storagedevice via a network, for example, the Internet, a local area network, awide area network and/or a wireless network. The network may comprisecopper transmission cables, optical transmission fibers, wirelesstransmission, routers, firewalls, switches, gateway computers and/oredge servers. A network adapter card or network interface in eachcomputing/processing device receives computer readable programinstructions from the network and forwards the computer readable programinstructions for storage in a computer readable storage medium withinthe respective computing/processing device.

Computer readable program instructions for carrying out operations ofthe present invention may be assembler instructions,instruction-set-architecture (ISA) instructions, machine instructions,machine dependent instructions, microcode, firmware instructions,state-setting data, or either source code or object code written in anycombination of one or more programming languages, including an objectoriented programming language such as Smalltalk, C++ or the like, andconventional procedural programming languages, such as the “C”programming language or similar programming languages. The computerreadable program instructions may execute entirely on the user'scomputer, partly on the user's computer, as a stand-alone softwarepackage, partly on the user's computer and partly on a remote computeror entirely on the remote computer or server. In the latter scenario,the remote computer may be connected to the user's computer through anytype of network, including a local area network (LAN) or a wide areanetwork (WAN), or the connection may be made to an external computer(for example, through the Internet using an Internet Service Provider).In some embodiments, electronic circuitry including, for example,programmable logic circuitry, field-programmable gate arrays (FPGA), orprogrammable logic arrays (PLA) may execute the computer readableprogram instructions by utilizing state information of the computerreadable program instructions to personalize the electronic circuitry,in order to perform aspects of the present invention.

Aspects of the present invention are described herein with reference toflowchart illustrations and/or block diagrams of methods, apparatus(systems), and computer program products according to embodiments of theinvention. It will be understood that each block of the flowchartillustrations and/or block diagrams, and combinations of blocks in theflowchart illustrations and/or block diagrams, can be implemented bycomputer readable program instructions.

These computer readable program instructions may be provided to aprocessor of a general purpose computer, special purpose computer, orother programmable data processing apparatus to produce a machine, suchthat the instructions, which execute via the processor of the computeror other programmable data processing apparatus, create means forimplementing the functions/acts specified in the flowchart and/or blockdiagram block or blocks. These computer readable program instructionsmay also be stored in a computer readable storage medium that can directa computer, a programmable data processing apparatus, and/or otherdevices to function in a particular manner, such that the computerreadable storage medium having instructions stored therein comprises anarticle of manufacture including instructions which implement aspects ofthe function/act specified in the flowchart and/or block diagram blockor blocks.

The computer readable program instructions may also be loaded onto acomputer, other programmable data processing apparatus, or other deviceto cause a series of operational steps to be performed on the computer,other programmable apparatus or other device to produce a computerimplemented process, such that the instructions which execute on thecomputer, other programmable apparatus, or other device implement thefunctions/acts specified in the flowchart and/or block diagram block orblocks.

The flowchart and block diagrams in the Figures illustrate thearchitecture, functionality, and operation of possible implementationsof systems, methods, and computer program products according to variousembodiments of the present invention. In this regard, each block in theflowchart or block diagrams may represent a module, segment, or portionof instructions, which comprises one or more executable instructions forimplementing the specified logical function(s). In some alternativeimplementations, the functions noted in the block may occur out of theorder noted in the figures. For example, two blocks shown in successionmay, in fact, be executed substantially concurrently, or the blocks maysometimes be executed in the reverse order, depending upon thefunctionality involved. It will also be noted that each block of theblock diagrams and/or flowchart illustration, and combinations of blocksin the block diagrams and/or flowchart illustration, can be implementedby special purpose hardware-based systems that perform the specifiedfunctions or acts or carry out combinations of special purpose hardwareand computer instructions.

The invention claimed is:
 1. A system for data replication comprising:at least one processor configured to: identify one or more complex datatransformations within database statements from source code files ofinstalled software products on a target system, wherein the source codefiles on the target system are updated versions relative tocorresponding ones of a source system, wherein the identified one ormore complex data transformations are stored in an order reflecting anorder of installation of the source code files on the target system, andwherein the one or more complex data transformations include one or morefrom a group of: moving data from plural table columns to a common tablecolumn, moving data from plural database tables to a common databasetable, and changing column data using a formula; create a subscriptionfor each complex data transformation, the subscription containinggenerated instructions executable on the target system for transformingdata within the source system into a form compatible with the targetsystem according to the complex data transformation; and execute theinstructions of the subscriptions within the target system during datareplication from the source system to the target system to perform thecomplex data transformations in the order reflecting the order ofinstallation of the source code files and transform source data of thesource system into a form compatible with the target system.
 2. Thesystem of claim 1, wherein the subscription comprises one or morereferences to one or more executable files containing instructions fortransforming data of the source system into a form compatible with thetarget system, the instructions comprising generated source code.
 3. Thesystem of claim 2, wherein the one or more executable files furthercomprise instructions providing fine grain access control to individualitems of data to be transformed, the fine grain access controlspecifying instructions for controlling access to one or more particulartables of data, rows of data, columns of data or variables to betransformed.
 4. The system of claim 3, wherein the fine grain accesscontrol includes, for a plurality of tables to be impacted by thecomplex data transformation: determining a subset of the plurality oftables comprising data that is not to be transformed; inactivatingaccess to the subset of tables; and performing the complex datatransformation only on the tables that have not been inactivated.
 5. Thesystem of claim 2, wherein the at least one processor is configured to:select a source code file of the target system containing a complex datatransformation; update source code of the source code file to transformdata of the source system into a form compatible with the target system;and save the updated source code.
 6. The system of claim 1, wherein theat least one processor is configured to: obtain the order ofinstallation of the source code files on the target system; parse thesource code files in the order of installation to identify acorresponding order of the complex data transformations; and generate acache of complex data transformations, wherein each table in the cachelists the identified complex data transformations in the correspondingorder based on the respective source code file.
 7. The system of claim1, wherein the at least one processor is configured to: generate areport listing differences between the source system and the targetsystem; identify from the report, items of data that are present withinthe target system; and generate a subscription for each complex datatransformation having a corresponding item of data in the target system.8. A computer program product for data replication comprising a computerreadable storage medium having computer readable program instructionsembodied therewith, the computer readable program instructions, whenexecuted by a processor, cause the processor to: identify one or morecomplex data transformations within database statements from source codefiles of installed software products on a target system, wherein thesource code files on the target system are updated versions relative tocorresponding ones of a source system, wherein the identified one ormore complex data transformations are stored in an order reflecting anorder of installation of the source code files on the target system, andwherein the one or more complex data transformations include one or morefrom a group of: moving data from plural table columns to a common tablecolumn, moving data from plural database tables to a common databasetable, and changing column data using a formula; create a subscriptionfor each complex data transformation, the subscription containinggenerated instructions executable on the target system for transformingdata within the source system into a form compatible with the targetsystem according to the complex data transformation; and execute theinstructions of the subscriptions within the target system during datareplication from the source system to the target system to perform thecomplex data transformations in the order reflecting the order ofinstallation of the source code files and transform source data of thesource system into a form compatible with the target system.
 9. Thecomputer program product of claim 8, wherein the subscription comprisesone or more references to one or more executable files containinginstructions for transforming data of the source system into a formcompatible with the target system, the instructions comprising generatedsource code.
 10. The computer program product of claim 9, wherein theone or more executable files further comprise instructions providingfine grain access control to individual items of data to be transformed,the fine grain access control specifying instructions for controllingaccess to one or more particular tables of data, rows of data, columnsof data or variables to be transformed.
 11. The computer program productof claim 10, wherein the fine grain access control includes, for aplurality of tables identified to be impacted by the complex datatransformation: determining a subset of the plurality of tablescomprising data that is not to be transformed; inactivating access tothe subset of tables; and performing the complex data transformationonly on the tables that have not been inactivated.
 12. The computerprogram product of claim 9, wherein the computer program product isconfigured to cause the processor to: select a source code file of thetarget system containing a complex data transformation; update sourcecode of the source code file to transform data of the source system intoa form compatible with the target system; and save the updated sourcecode file.
 13. The computer program product of claim 8, wherein thecomputer program product is configured to cause the processor to: obtainthe order of installation of the source code files on the target system;parse the source code files in the order of installation to identify acorresponding order of the complex data transformations; and generate acache of complex data transformations, wherein each table in the cachelists the identified complex data transformations in the correspondingorder based on the respective source code file.
 14. The computer programproduct of claim 8, wherein the computer program product is configuredto cause the processor to: generate a report listing differences betweenthe source system and the target system; identify from the report, itemsof data that are present within the target system; and generate asubscription for each complex data transformation having a correspondingitem of data in the target system.